use mysql::*;
use mysql::prelude::*;
use sqlparser::parser::Parser;
use sqlparser::dialect::MySqlDialect;
use sqlparser::ast::Statement;
use lazy_static::lazy_static;
use serde::{Serialize, Deserialize};
use std::{collections::HashMap, sync::MutexGuard};
use std::fs;
use std::sync::Mutex;
use dirs::config_dir;
// use log::{info, error};

const CONFIG_DIR_NAME: &str = "com.tauri.TableOne";
const CONFIG_FILE_NAME: &str = "db_config.json";

lazy_static! {
    static ref DB_CONNECTIONS: Mutex<HashMap<String, PooledConn>> = Mutex::new(HashMap::new());
}

#[derive(Debug, Serialize)]
struct FindTableResult {
    data: Vec<HashMap<String, (String, String)>>,
    columns: Vec<String>,
    total: usize,
    allow_edit: usize,
    primary_key: Vec<String>,
    table_name: String,
}

#[derive(Debug, Deserialize, Clone)]
struct CreateTableRequest {
    name: String,
    engine: String,
    charset: String,
    collation: String,
    comment: String,
    columns: Vec<HashMap<String, String>>,
    operate: String,
}

#[derive(Debug, Serialize)]
struct ColumnValue {
    old_name: String,
    name: String,
    type_: String,
    length: String,
    attribute: String,
    val: String,
    nullable: String,
    ai: String,
    comment: String,
    after: String,
}

#[tauri::command]
fn add_server(index: i16, label: &str, host: &str, port: &str, user: &str, password: &str) -> Result<Vec<HashMap<String, String>>, String> {
    let mut config = read_config()?;

    let server = HashMap::from([
        ("label".to_string(), label.to_string()),
        ("host".to_string(), host.to_string()),
        ("port".to_string(), port.to_string()),
        ("user".to_string(), user.to_string()),
        ("password".to_string(), password.to_string()),
    ]);

    match index {
        -1 => config.push(server),
        _ => {
            let _index = index as usize;
            if let Some(server_entry) = config.get_mut(_index) {
                *server_entry = server;
            } else {
                return Err(format!("Index {} 不存在", _index));
            }
        }
    }

    write_config(&config)?;
    Ok(config)
}

#[tauri::command]
fn del_server(index: usize) -> Result<(), String> {
    let mut config = read_config()?;

    if index >= config.len() {
        return Err(format!("Index {} 不存在", index));
    }

    config.remove(index);
    write_config(&config)?;
    Ok(())
}

#[tauri::command]
fn server_list() -> Result<Vec<HashMap<String, String>>, String> {
    read_config()
}

#[tauri::command]
fn sort_server(sort: Vec<String>) -> Result<Vec<HashMap<String, String>>, String> {
    let mut config = read_config()?;
    config.sort_by(|a, b| {
        let a_key = a.get("label").unwrap();
        let b_key = b.get("label").unwrap();
        sort.iter().position(|x| x == a_key).unwrap_or(usize::MAX).cmp(&sort.iter().position(|x| x == b_key).unwrap_or(usize::MAX))
    });
    write_config(&config)?;
    Ok(config)
}

#[tauri::command]
fn db_list(host: &str, port: &str, user: &str, password: &str) -> Result<Vec<String>, String> {
    let opts = build_opts(host, port, user, password)?;
    let pool = Pool::new(opts).map_err(|e|e.to_string())?;
    let mut conn = pool.get_conn().map_err(|e|e.to_string())?;
    let sql = "SHOW DATABASES";
    conn.query_map(sql, |Database| {Database}).map_err(|e|e.to_string())
}

#[tauri::command]
fn connect_db(host: &str, port: &str, user: &str, password: &str, database: &str) -> Result<Vec<String>, String> {
    let connkey = format!("{}:{}", host, database);
    let mut connections = get_db_connections().map_err(|e|e.to_string())?;
    let conn;

    let opts = build_opts(host, port, user, password).map_err(|e| e.to_string())?.db_name(Some(database));
    let pool = Pool::new(opts).map_err(|e| e.to_string())?;
    let pconn = pool.get_conn().map_err(|e| e.to_string())?;
    connections.insert(connkey.clone(), pconn);
    conn = connections.get_mut(&connkey).unwrap();

    let sql = format!("SELECT TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{}';", database);
    let tables = conn.query_map(sql, |table_name| table_name).map_err(|e| e.to_string())?;

    Ok(tables)
}

#[tauri::command]
fn create_table_preview(json: &str) -> Result<String, String> {
    let request: CreateTableRequest = serde_json::from_str(json).map_err(|e|e.to_string())?;
    let (columns, primary) = table_from_columns(request.clone()).map_err(|e|e.to_string())?;
    let mut column_str = String::new();
    for col in columns {
        if !column_str.is_empty() {
            column_str += ", ";
        }
        column_str += &format!("`{}` {}{} {} {} {} {} {}", col.name, col.type_, col.length, col.attribute, col.nullable, col.val, col.ai, col.comment);
    }
    if !primary.is_empty() {
        column_str += &format!(", PRIMARY KEY ({})", primary.join(","));
    }
    let sql = format!("CREATE TABLE `{}` ({}) ENGINE = {} CHARACTER SET {} COLLATE {} COMMENT = '{}';", 
        request.name, column_str, request.engine, request.charset, request.collation, request.comment);

    Ok(sql)
}

#[tauri::command]
fn create_table(host: &str, database: &str, json: &str) -> Result<(), String> {
    let connkey = format!("{}:{}", host, database);
    let mut conn = get_db_connections().map_err(|e|e.to_string())?;
    let conn = conn.get_mut(&connkey).ok_or("数据库连接不存在")?;
    // println!("{}", json);
    
    let sql = create_table_preview(json)?;
    // println!("{}", sql);
    conn.query_drop(sql).map_err(|e|e.to_string())?;
    Ok(())
}

#[tauri::command]
fn alter_table_preview(json: &str) -> Result<String, String> {
    let request: CreateTableRequest = serde_json::from_str(json).map_err(|e|e.to_string())?;
    let (columns, _) = table_from_columns(request.clone()).map_err(|e|e.to_string())?;

    let mut column_str = String::new();
    for col in columns {
        if !column_str.is_empty() {
            column_str += ", ";
        }
        if request.operate == "CHANGE" {
            column_str += &format!("CHANGE {} `{}` {}{} {} {} {} {} {}", col.old_name, col.name, col.type_, col.length, col.attribute, col.nullable, col.val, col.ai, col.comment);
        } else if request.operate == "ADD" {
            column_str += &format!("ADD {} `{}` {}{} {} {} {} {} {} {}", col.old_name, col.name, col.type_, col.length, col.attribute, col.nullable, col.val, col.ai, col.comment, col.after);
        }
    }
    let sql = format!("ALTER TABLE `{}` {};", request.name, column_str);
    Ok(sql)
}

#[tauri::command]
fn alter_table(host: &str, database: &str, json: &str) -> Result<(), String> {
    let connkey = format!("{}:{}", host, database);
    let mut conn = get_db_connections().map_err(|e|e.to_string())?;
    let conn = conn.get_mut(&connkey).ok_or("数据库连接不存在")?;
    let sql = alter_table_preview(json)?;

    // println!("{}", sql);
    conn.query_drop(sql).map_err(|e|e.to_string())?;
    Ok(())
}

#[tauri::command]
fn table_index(host: &str, database: &str, table: &str) -> Result<Vec<HashMap<String, Option<String>>>, String> {
    let connkey = format!("{}:{}", host, database);
    let mut conn = get_db_connections().map_err(|e|e.to_string())?;
    let conn = conn.get_mut(&connkey).ok_or("数据库连接不存在")?;
    let sql = format!("SHOW INDEX FROM {} ", table);
    let result = conn.query_map(&sql, |row: Row| row).map_err(|err| err.to_string())?;
    let mut data: Vec<HashMap<String, Option<String>>> = Vec::new();
    for row in result {
        let mut map: HashMap<String, Option<String>> = HashMap::new();
        map.insert("Key_name".to_string(), from_value(row["Key_name"].clone()));
        map.insert("Column_name".to_string(), from_value(row["Column_name"].clone()));
        map.insert("Non_unique".to_string(), from_value(row["Non_unique"].clone()));
        map.insert("Seq_in_index".to_string(), from_value(row["Seq_in_index"].clone()));
        map.insert("Index_type".to_string(), from_value(row["Index_type"].clone()));
        map.insert("Comment".to_string(), from_value(row["Comment"].clone()));
        data.push(map);
    }
    Ok(data)
}

#[tauri::command]
fn table_columns(host: &str, database: &str, table: &str, column: &str) -> Result<Vec<HashMap<String, Option<String>>>, String> {
    let connkey = format!("{}:{}", host, database);
    let mut conn = get_db_connections().map_err(|e|e.to_string())?;
    let conn = conn.get_mut(&connkey).ok_or("数据库连接不存在")?;
    let sql = format!("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{}' AND TABLE_NAME = '{}'", database, table);
    let sql = if column.is_empty() {
        sql
    } else {
        format!("{} AND COLUMN_NAME = '{}'", sql, column)
    };
    let result = conn.query_map(&sql, |row: Row| row).map_err(|err| err.to_string())?;
    let mut data: Vec<HashMap<String, Option<String>>> = Vec::new();
    for row in result {
        let mut map: HashMap<String, Option<String>> = HashMap::new();
        map.insert("name".to_string(), from_value(row["COLUMN_NAME"].clone()));
        let default = row["COLUMN_DEFAULT"].clone();
        if default == Value::NULL {
            map.insert("default".to_string(), None);
        } else {
            map.insert("default".to_string(), from_value(default));
        }
        map.insert("data_type".to_string(), from_value(row["DATA_TYPE"].clone()));
        map.insert("nullable".to_string(), from_value(row["IS_NULLABLE"].clone()));
        map.insert("type".to_string(), from_value(row["COLUMN_TYPE"].clone()));
        map.insert("length".to_string(), from_value(row["CHARACTER_MAXIMUM_LENGTH"].clone()));
        map.insert("comment".to_string(), from_value(row["COLUMN_COMMENT"].clone()));
        let collation = row["COLLATION_NAME"].clone();
        if collation == Value::NULL {
            map.insert("collation".to_string(), None);
        } else {
            map.insert("collation".to_string(), from_value(collation));
        }
        map.insert("key".to_string(), from_value(row["COLUMN_KEY"].clone()));
        map.insert("extra".to_string(), from_value(row["EXTRA"].clone()));
        data.push(map);
    };
    Ok(data)
}

#[tauri::command]
fn execute_sql(host: &str, database: &str, sql: &str, pagesize: usize, page: usize) -> Result<FindTableResult, String> {
    let connkey = format!("{}:{}", host, database);
    let mut conn = get_db_connections().map_err(|e|e.to_string())?;
    let conn = conn.get_mut(&connkey).ok_or("数据库连接不存在")?;
    if sql.trim().is_empty() {
        return Err("sql不能为空".to_string());
    }
    
    let sql = sql.trim_end_matches(';');
    // let sql = sql.trim_end_matches(';').to_lowercase();
    // println!("sql: {}", sql);
    let dialect = MySqlDialect {};
    let ast = Parser::parse_sql(&dialect, &sql).map_err(|e|e.to_string())?;

    if ast.is_empty() {
        return Err("sql不能为空".to_string());
    }
    if ast.len() > 1 {
        return Err("暂不支持执行多条sql".to_string());
    }

    match &ast[0] {
        Statement::Query(_) | Statement::ShowVariable { .. } | Statement::ShowVariables { .. } | Statement::ShowCreate { .. } | Statement::ShowColumns { .. } | Statement::ExplainTable { .. } =>{
            _query_sql(conn,database.to_string(), sql.to_string(), pagesize,page)
        },
        _ => {
            _exec_sql(conn,sql.to_string())
        },
    }
}

fn _query_sql(conn: &mut PooledConn, database: String, mut sql: String, pagesize: usize, page: usize) -> Result<FindTableResult, String> {
    let mut total: usize = 0;
    let mut data: Vec<HashMap<String, (String, String)>> = Vec::new();
    let mut columns: Vec<String> = Vec::new();
    let mut primary_key: Vec<String> = Vec::new();
    let mut allow_edit: usize = 0;
    let mut is_join = 0;
    let mut sql_total: String;
    let mut table_name = "".to_string();

    sql = sql.to_lowercase();
    let index: usize = match sql.find("from") {
        Some(_index) => _index,
        None => 0,
    };

    if sql.starts_with("select") {
        if database != "information_schema" && index > 0 {
            table_name = sql[index + 4..].trim().split(' ').next().unwrap().to_string();
            //如果是非条件查询，则从schema中获取总数，否则转换成count
            if !sql.contains(" where ") && !sql.contains(" group ") {
                sql_total = format!("SELECT TABLE_ROWS AS cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{}' AND TABLE_NAME = '{}';", database, table_name);
            } else if sql.contains(" distinct ") {
                let _idx = sql.find("from").unwrap();
                sql_total = format!("SELECT COUNT(DISTINCT {}) AS cnt {}", sql[sql.find("distinct").unwrap() + 8.._idx].trim(), sql[_idx..].trim());
            } else {
                let _idx = sql.find("from").unwrap();
                sql_total = sql.to_string();
                sql_total.replace_range(.._idx, "SELECT COUNT(*) AS cnt ");
            }
            let result = conn.query_map(&sql_total, |row: Row| row).map_err(|err| err.to_string())?;
            if let Some(row) = result.first() {
                let value = &row["cnt"];
                total  = from_value(value.clone());
            }
        }

        if !sql.contains(" limit ") {
            let offset = (page - 1) * pagesize;
            sql = format!("{} LIMIT {}, {}", sql, offset, pagesize);
        } else {
            total = 0;
        }

        is_join = sql.contains(" join ") as usize;
    }

    let result: Vec<Row> = conn.query_map(sql, |row: Row| row).map_err(|err| err.to_string())?;
    let mut column_check: bool = false;
    for row in result {
        let mut map = HashMap::new();
        for column in row.columns_ref() {
            let column_value = &row[column.name_str().as_ref()];
        
            // println!(
            //     "Column {} of type {:?} with value {:?}",
            //     column.name_str(),
            //     column.column_type(),
            //     column.flags(), 
            // );
            if !column_check {
                if column.flags().contains(consts::ColumnFlags::PRI_KEY_FLAG) {
                    primary_key.push(column.name_str().to_string());
                }
                columns.push(column.name_str().to_string());
            }
            let value: (String, String);
            if column_value == &Value::NULL {
                value = ("NULL".to_string(), "NULL".to_string());
            } else {
                value = ("String".to_string(), from_value(column_value.clone()));
            }
            map.insert(column.name_str().to_string(), value);
        }
        data.push(map);
        column_check = true;
    }

    if total == 0 {
        total = data.len();
    }

    if is_join == 0 && primary_key.len() > 0 {
        allow_edit = 1;
    }
    Ok(FindTableResult{data, columns, total, allow_edit, primary_key, table_name})
}

fn _exec_sql(conn: &mut PooledConn, sql: String) -> Result<FindTableResult, String> {
    let total: usize = 0;
    let primary_key: Vec<String> = Vec::new();
    let allow_edit: usize = 0;
    let mut data: Vec<HashMap<String, (String, String)>> = Vec::new();
    let mut columns: Vec<String> = Vec::new();
    let table_name = "".to_string();

    conn.query_drop(sql).map_err(|err| err.to_string())?;
    columns.push("Rows affected".to_string());
    let mut row = HashMap::new();
    row.insert("Rows affected".to_string(), ("String".to_string(), conn.affected_rows().to_string()));
    data.push(row);
    
    Ok(FindTableResult{data, columns, total, allow_edit, primary_key, table_name})
}

fn read_config() -> Result<Vec<HashMap<String, String>>, String> {
    match config_dir() {
        None => Err("无法获取配置目录".to_string()),
        Some(dir) => {
            let dir = dir.join(CONFIG_DIR_NAME);
            if !dir.exists() {
                fs::create_dir_all(&dir).map_err(|e| e.to_string())?;
            }
            let path = dir.join(CONFIG_FILE_NAME);
            if !path.exists() {
                let empty_config: Vec<HashMap<String, String>> = Vec::new();
                let config_str = serde_json::to_string(&empty_config).map_err(|e| e.to_string())?;
                fs::write(path, config_str).map_err(|e| e.to_string())?;
                return Ok(empty_config);
            }
            let contents = fs::read_to_string(path).map_err(|e| e.to_string())?;
            serde_json::from_str(&contents).map_err(|e| e.to_string())
        }
    }
}

fn write_config(config: &Vec<HashMap<String, String>>) -> Result<(), String> {
    match config_dir() {
        None => Err("无法获取配置目录".to_string()),
        Some(dir) => {
            let path = dir.join(CONFIG_DIR_NAME.to_string() + "/" + CONFIG_FILE_NAME);
            let config_str = serde_json::to_string(config).map_err(|e| e.to_string())?;
            fs::write(path, config_str).map_err(|e| e.to_string())
        }
    }
}

fn build_opts(host: &str, port: &str, user: &str, password: &str) -> Result<OptsBuilder, String> {
    let mut cnf_map = HashMap::new();
    cnf_map.insert("host".to_string(), host.to_string());
    cnf_map.insert("port".to_string(), port.to_string());
    cnf_map.insert("user".to_string(), user.to_string());
    cnf_map.insert("password".to_string(), password.to_string());
    cnf_map.insert("tcp_connect_timeout_ms".to_string(), "5000".to_string());
    cnf_map.insert("tcp_keepalive_time_ms".to_string(), "300000".to_string());
    cnf_map.insert("tcp_keepalive_probe_interval_secs".to_string(), "30".to_string());
    cnf_map.insert("tcp_keepalive_probe_count".to_string(), "5".to_string());
    OptsBuilder::new().from_hash_map(&cnf_map).map_err(|e| e.to_string())
}

fn get_db_connections() -> Result<MutexGuard<'static, HashMap<String, PooledConn>>, String> {
    match DB_CONNECTIONS.lock() {
        Ok(guard) => Ok(guard),
        Err(poisoned) => {
            Ok(poisoned.into_inner())
        }
    }
}

fn table_from_columns(request: CreateTableRequest) -> Result<(Vec<ColumnValue>, Vec<String>), String> {
    let mut columns: Vec<ColumnValue> = vec![];
    let mut primary: Vec<String> = vec![];
    let mut prev_name = "".to_string();
    for item in request.columns {
        if item["name"].is_empty() {
            continue;
        }

        let _length: String;
        let _null: String;
        let _value: String;
        let _comment: String;
        let _ai: String;
        let _after: String;
        if item["length"].is_empty() {
            _length = "".to_string();
        }else{
            _length = format!("({})", item["length"]);
        }

        if item["null"] == "1" {
            _null = "NULL".to_string();
        }else{
            _null = "NOT NULL".to_string();
        }

        if item["value"] == "NONE" {
            _value = "".to_string();
        } else if item["value"] == "DEFINED" {
            _value = format!("DEFAULT '{}'", item["definedValue"]);
        }else{
            _value = format!("DEFAULT {}", item["value"]);
        }

        if item["comment"].is_empty() {
            _comment = "".to_string();
        }else{
            _comment = format!("COMMENT '{}'", item["comment"]);
        }

        if item["auto_increment"] == "1" {
            _ai = "AUTO_INCREMENT".to_string();
        }else{
            _ai = "".to_string();
        }

        if item["primary"] == "1" {
            primary.push(format!("`{}`", item["name"]));
        }
        let old_name = if item["old_name"].is_empty() {
            "".to_string()
        } else {
            format!("`{}`", item["old_name"])
        };

        _after = if prev_name.is_empty() {
            if !item.contains_key("after") || item["after"].is_empty() {
                "".to_string()
            } else {
                format!("AFTER `{}`", item["after"])
            }
        } else {
            format!("AFTER `{}`", prev_name)
        };

        prev_name = item["name"].clone();
        let col = ColumnValue{
            old_name: old_name,
            name: item["name"].clone(),
            type_: item["type"].clone(),
            length: _length,
            attribute: item["attribute"].clone(),
            nullable: _null,
            val: _value,
            comment: _comment,
            ai: _ai,
            after: _after,
        };
        columns.push(col);
    }
    Ok((columns, primary))
}

#[cfg_attr(mobile, tauri::mobile_entry_point)]
pub fn run() {
    tokio::runtime::Builder::new_multi_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            tauri::Builder::default()
                .plugin(tauri_plugin_opener::init())
                .invoke_handler(tauri::generate_handler![
                    add_server,
                    del_server,
                    server_list,
                    sort_server,
                    db_list,
                    connect_db,
                    execute_sql,
                    create_table_preview,
                    create_table,
                    alter_table_preview,
                    alter_table,
                    table_index,
                    table_columns
                ])
                .run(tauri::generate_context!())
                .expect("error while running tauri application");
        });
}
